﻿Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel.Constants
Imports Microsoft.Office.Interop.Excel.XlLineStyle

Public Class Utilidades
    '' Creamos un objeto Excel
    Private Shared m_Excel As Excel.Application
    Private Shared empresa As GBDZafiro.Empresa
    Private Shared columna As String

    Public Shared Function ExportarExcel(ByRef datos As DataTable, ByRef titulo As String) As GBDZafiro.Resultado
        Dim result As New GBDZafiro.Resultado
        Try

            Dim i As Integer = 8
            '' Creamos un objeto WorkBook
            Dim objLibroExcel As Excel.Workbook
            '' Creamos un objeto WorkSheet
            Dim objHojaExcel As Excel.Worksheet
            crearPlantilla(objLibroExcel, objHojaExcel, datos, titulo)

            Dim strRangoTotal As String = "A7:" & "A" & (datos.Rows.Count + 7).ToString
            Dim objRango As Excel.Range = objHojaExcel.Range(strRangoTotal)
            objRango.NumberFormat = "@"

            For Each rProd As DataRow In datos.Rows
                For index = 0 To datos.Columns.Count() - 1
                    objHojaExcel.Cells(i, index + 1) = rProd.Item(index)
                Next
                i += 1
            Next

            strRangoTotal = "A7:" & nombreColumna(datos.Columns.Count) & (datos.Rows.Count + 7).ToString
            '' Seleccionar todo el bloque desde A1 hasta D #de filas 
            objRango = objHojaExcel.Range(strRangoTotal)
            objRango.Select()
            objRango.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic)
            objRango.Columns.AutoFit()

            m_Excel.Visible = True
            result.ExisteError = True
            result.Mensaje = "El archivo fue generado satisfactoriamente "
            Return result

        Catch ex As Exception
            result.ExisteError = False
            result.Mensaje = ex.Message
            Return result
        End Try
    End Function

    Private Shared Sub crearPlantilla(ByRef objLibroExcel As Excel.Workbook, ByRef objHojaExcel As Excel.Worksheet, _
                               ByRef datos As DataTable, ByRef titulo As String)

        '' Iniciamos una instancia a Excel
        m_Excel = New Excel.Application
        'm_Excel.Visible = True

        '' Creamos una variable para guardar la cultura actual
        Dim OldCultureInfo As System.Globalization.CultureInfo = _
            System.Threading.Thread.CurrentThread.CurrentCulture

        'Crear una cultura standard (en-US) inglés estados unidos
        System.Threading.Thread.CurrentThread.CurrentCulture = _
            New System.Globalization.CultureInfo("en-US")

        '' Creamos una instancia del Workbooks de Excel
        '' Creamos una instancia de la primera hoja de trabajo de Excel
        objLibroExcel = m_Excel.Workbooks.Add()
        objHojaExcel = objLibroExcel.Worksheets(1)
        objHojaExcel.Visible = Excel.XlSheetVisibility.xlSheetVisible

        '' Hacemos esta hoja la visible en pantalla
        '' (como seleccionamos la primera esto no es necesario
        '' si seleccionamos una diferente a la primera si lo
        '' necesitaríamos, esto lo hacemos como forma de mostrar como
        ''cambiar de entre hojas en un documento Excel).
        objHojaExcel.Activate()

        ''Creamos la Empresa 
        empresa = (New Tools).crearEmpresa()
        columna = nombreColumna(datos.Columns.Count())

        '' Crear el encabezado de nuestro informe
        objHojaExcel.Range("A1:" + columna + "1").Merge()
        objHojaExcel.Range("A1:" + columna + "1").Value = empresa.RAZON_SOCIAL.Trim
        objHojaExcel.Range("A1:" + columna + "1").Font.Bold = True
        objHojaExcel.Range("A1:" + columna + "1").Font.Size = 15
        objHojaExcel.Range("A1:" + columna + "1").HorizontalAlignment = xlCenter

        '' Crear el subencabezado de nuestro informe
        objHojaExcel.Range("A2:" + columna + "2").Merge()
        objHojaExcel.Range("A2:" + columna + "2").Value = "Nit: " + empresa.NIT.Trim
        objHojaExcel.Range("A2:" + columna + "2").Font.Italic = True
        objHojaExcel.Range("A2:" + columna + "2").Font.Bold = False
        objHojaExcel.Range("A2:" + columna + "2").Font.Size = 13
        objHojaExcel.Range("A2:" + columna + "2").HorizontalAlignment = xlCenter

        '' Crear el subencabezado de nuestro informe
        objHojaExcel.Range("A3:" + columna + "3").Merge()
        objHojaExcel.Range("A3:" + columna + "3").Value = "Dirección: " + empresa.DIRECCION.Trim + " " + _
            "Telefono: " + empresa.TELEFONO.Trim
        objHojaExcel.Range("A3:" + columna + "3").Font.Italic = True
        objHojaExcel.Range("A3:" + columna + "3").Font.Bold = False
        objHojaExcel.Range("A3:" + columna + "3").Font.Size = 13
        objHojaExcel.Range("A3:" + columna + "3").HorizontalAlignment = xlCenter

        objHojaExcel.Range("A4:" + columna + "4").Merge()

        '' Crear el subencabezado de nuestro informe
        objHojaExcel.Range("A5:" + columna + "5").Merge()
        objHojaExcel.Range("A5:" + columna + "5").Value = titulo.Trim
        objHojaExcel.Range("A5:" + columna + "5").Font.Italic = True
        objHojaExcel.Range("A5:" + columna + "5").Font.Bold = True
        objHojaExcel.Range("A5:" + columna + "5").Font.Size = 13
        objHojaExcel.Range("A5:" + columna + "5").HorizontalAlignment = xlCenter

        Dim objCelda As Excel.Range
        For index = 0 To datos.Columns.Count() - 1
            objCelda = objHojaExcel.Range(nombreColumna(index + 1) + "7", Type.Missing)
            objCelda.Value = datos.Columns.Item(index).ColumnName.Trim
            objCelda.Font.Bold = True
            objCelda.Interior.Color = Color.LightBlue
            objCelda.HorizontalAlignment = xlCenter
            objCelda.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic)
        Next

    End Sub

    Private Shared Function nombreColumna(ByVal numero As Integer) As String
        Dim columna(256) As String

        columna(1) = "A"
        columna(2) = "B"
        columna(3) = "C"
        columna(4) = "D"
        columna(5) = "E"
        columna(6) = "F"
        columna(7) = "G"
        columna(8) = "H"
        columna(9) = "I"
        columna(10) = "J"
        columna(11) = "K"
        columna(12) = "L"
        columna(13) = "M"
        columna(14) = "N"
        columna(15) = "O"
        columna(16) = "P"
        columna(17) = "Q"
        columna(18) = "R"
        columna(19) = "S"
        columna(20) = "T"
        columna(21) = "U"
        columna(22) = "V"
        columna(23) = "W"
        columna(24) = "X"
        columna(25) = "Y"
        columna(26) = "Z"
        columna(27) = "AA"
        columna(28) = "AB"
        columna(29) = "AC"
        columna(30) = "AD"
        columna(31) = "AE"
        columna(32) = "AF"
        columna(33) = "AG"
        columna(34) = "AH"
        columna(35) = "AI"
        columna(36) = "AJ"
        columna(37) = "AK"
        columna(38) = "AL"
        columna(39) = "AM"
        columna(40) = "AN"
        columna(41) = "AO"
        columna(42) = "AP"
        columna(43) = "AQ"
        columna(44) = "AR"
        columna(45) = "AS"
        columna(46) = "AT"
        columna(47) = "AU"
        columna(48) = "AV"
        columna(49) = "AW"
        columna(50) = "AX"
        columna(51) = "AY"
        columna(52) = "AZ"
        columna(53) = "BA"
        columna(54) = "BB"
        columna(55) = "BC"
        columna(56) = "BD"
        columna(57) = "BE"
        columna(58) = "BF"
        columna(59) = "BG"
        columna(60) = "BH"
        columna(61) = "BI"
        columna(62) = "BJ"
        columna(63) = "BK"
        columna(64) = "BL"
        columna(65) = "BM"
        columna(66) = "BN"
        columna(67) = "BO"
        columna(68) = "BP"
        columna(69) = "BQ"
        columna(70) = "BR"
        columna(71) = "BS"
        columna(72) = "BT"
        columna(73) = "BU"
        columna(74) = "BV"
        columna(75) = "BW"
        columna(76) = "BX"
        columna(77) = "BY"
        columna(78) = "BZ"
        columna(79) = "CA"
        columna(80) = "CB"
        columna(81) = "CC"
        columna(82) = "CD"
        columna(83) = "CE"
        columna(84) = "CF"
        columna(85) = "CG"
        columna(86) = "CH"
        columna(87) = "CI"
        columna(88) = "CJ"
        columna(89) = "CK"
        columna(90) = "CL"
        columna(91) = "CM"
        columna(92) = "CN"
        columna(93) = "CO"
        columna(94) = "CP"
        columna(95) = "CQ"
        columna(96) = "CR"
        columna(97) = "CS"
        columna(98) = "CT"
        columna(99) = "CU"
        columna(100) = "CV"
        columna(101) = "CW"
        columna(102) = "CX"
        columna(103) = "CY"
        columna(104) = "CZ"
        columna(105) = "DA"
        columna(106) = "DB"
        columna(107) = "DC"
        columna(108) = "DD"
        columna(109) = "DE"
        columna(110) = "DF"
        columna(111) = "DG"
        columna(112) = "DH"
        columna(113) = "DI"
        columna(114) = "DJ"
        columna(115) = "DK"
        columna(116) = "DL"
        columna(117) = "DM"
        columna(118) = "DN"
        columna(119) = "DO"
        columna(120) = "DP"
        columna(121) = "DQ"
        columna(122) = "DR"
        columna(123) = "DS"
        columna(124) = "DT"
        columna(125) = "DU"
        columna(126) = "DV"
        columna(127) = "DW"
        columna(128) = "DX"
        columna(129) = "DY"
        columna(130) = "DZ"
        columna(131) = "EA"
        columna(132) = "EB"
        columna(133) = "EC"
        columna(134) = "ED"
        columna(135) = "EE"
        columna(136) = "EF"
        columna(137) = "EG"
        columna(138) = "EH"
        columna(139) = "EI"
        columna(140) = "EJ"
        columna(141) = "EK"
        columna(142) = "EL"
        columna(143) = "EM"
        columna(144) = "EN"
        columna(145) = "EO"
        columna(146) = "EP"
        columna(147) = "EQ"
        columna(148) = "ER"
        columna(149) = "ES"
        columna(150) = "ET"
        columna(151) = "EU"
        columna(152) = "EV"
        columna(153) = "EW"
        columna(154) = "EX"
        columna(155) = "EY"
        columna(156) = "EZ"
        columna(157) = "FA"
        columna(158) = "FB"
        columna(159) = "FC"
        columna(160) = "FD"
        columna(161) = "FE"
        columna(162) = "FF"
        columna(163) = "FG"
        columna(164) = "FH"
        columna(165) = "FI"
        columna(166) = "FJ"
        columna(167) = "FK"
        columna(168) = "FL"
        columna(169) = "FM"
        columna(170) = "FN"
        columna(171) = "FO"
        columna(172) = "FP"
        columna(173) = "FQ"
        columna(174) = "FR"
        columna(175) = "FS"
        columna(176) = "FT"
        columna(177) = "FU"
        columna(178) = "FV"
        columna(179) = "FW"
        columna(180) = "FX"
        columna(181) = "FY"
        columna(182) = "FZ"
        columna(183) = "GA"
        columna(184) = "GB"
        columna(185) = "GC"
        columna(186) = "GD"
        columna(187) = "GE"
        columna(188) = "GF"
        columna(189) = "GG"
        columna(190) = "GH"
        columna(191) = "GI"
        columna(192) = "GJ"
        columna(193) = "GK"
        columna(194) = "GL"
        columna(195) = "GM"
        columna(196) = "GN"
        columna(197) = "GO"
        columna(198) = "GP"
        columna(199) = "GQ"
        columna(200) = "GR"
        columna(201) = "GS"
        columna(202) = "GT"
        columna(203) = "GU"
        columna(204) = "GV"
        columna(205) = "GW"
        columna(206) = "GX"
        columna(207) = "GY"
        columna(208) = "GZ"
        columna(209) = "HA"
        columna(210) = "HB"
        columna(211) = "HC"
        columna(212) = "HD"
        columna(213) = "HE"
        columna(214) = "HF"
        columna(215) = "HG"
        columna(216) = "HH"
        columna(217) = "HI"
        columna(218) = "HJ"
        columna(219) = "HK"
        columna(220) = "HL"
        columna(221) = "HM"
        columna(222) = "HN"
        columna(223) = "HO"
        columna(224) = "HP"
        columna(225) = "HQ"
        columna(226) = "HR"
        columna(227) = "HS"
        columna(228) = "HT"
        columna(229) = "HU"
        columna(230) = "HV"
        columna(231) = "HW"
        columna(232) = "HX"
        columna(233) = "HY"
        columna(234) = "HZ"
        columna(235) = "IA"
        columna(236) = "IB"
        columna(237) = "IC"
        columna(238) = "ID"
        columna(239) = "IE"
        columna(240) = "IF"
        columna(241) = "IG"
        columna(242) = "IH"
        columna(243) = "II"
        columna(244) = "IJ"
        columna(245) = "IK"
        columna(246) = "IL"
        columna(247) = "IM"
        columna(248) = "IN"
        columna(249) = "IO"
        columna(250) = "IP"
        columna(251) = "IQ"
        columna(252) = "IR"
        columna(253) = "IS"
        columna(254) = "IT"
        columna(255) = "IU"
        columna(256) = "IV"

        Return columna(numero)
    End Function


End Class
